package com.ymttest.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import org.apache.http.client.ClientProtocolException;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.ymt.core.tool.Logger;

public class ExcleUtil {

	private Workbook wb = null;
	private XSSFSheet sheet = null;
	private Row row = null;
	private Cell cell = null;
	// 当前时间
	private String strCurrentTime = new SimpleDateFormat("yyyyMMddHHmmss")
			.format(Calendar.getInstance().getTime());
	// 文件保存路径 桌面上
	private String strPath = System.getProperty("user.home") + "\\Desktop\\";
	//demo
	public void create() {
		String fileName = "test_" + strCurrentTime + ".xlsx";
		wb = new XSSFWorkbook();
		sheet = (XSSFSheet) wb.createSheet("test");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		cell.setCellValue("just to test creater excel file");
		File file = new File(strPath + fileName);
		FileOutputStream fos = null;
		Logger.debug("path:" + strPath + fileName);
		try {
			fos = new FileOutputStream(file);
			wb.write(fos);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (fos != null) {
				try {
					fos.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	//demo
	public void readExcel() {
		String xlsx2007 = "test.xlsx";
		InputStream io = null;
		try {
			File file = new File(strPath + xlsx2007);
			io = new FileInputStream(file);
			// 2003 xls
			// Workbook wb=new HSSFWorkbook(is);
			// 2007 xlsx格式
			// Workbook wb = new XSSFWorkbook(excelFile);
			wb = new XSSFWorkbook(io);
			sheet = (XSSFSheet) wb.getSheetAt(0);
			row = sheet.getRow(0);
			if (row == null)
				Logger.debug("row is null");
			cell = row.getCell(0);
			String cellStr;
			if (cell == null) {
				cellStr = "";
			} else if (cell.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
				cellStr = String.valueOf(cell.getBooleanCellValue());
			} else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
				cellStr = cell.getNumericCellValue() + "";
			} else {
				cellStr = cell.getStringCellValue();
			}
			Logger.debug("cell str:" + cellStr);
			// cell.setCellValue("test 2007");
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (io != null) {
				try {
					io.close();
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}

		}

	}
	
	public void writeResult(ArrayList<TestCaseBean> list) {
		if (list.size() == 0) {
			Logger.debug("List 为null 退出");
			return;
		}
		String fileName = "testCase_" + strCurrentTime + ".xlsx";
		wb = new XSSFWorkbook();
		// 创建一个表
		sheet = (XSSFSheet) wb.createSheet("testCase");
		// 设置字体
		XSSFFont font = (XSSFFont) wb.createFont();
		// 设置样式
		XSSFCellStyle headerStyle = (XSSFCellStyle) wb.createCellStyle();
		XSSFCellStyle cellStyle = (XSSFCellStyle) wb.createCellStyle();
		XSSFCellStyle cancelStyle = (XSSFCellStyle) wb.createCellStyle();
		XSSFCellStyle ignoreStyle = (XSSFCellStyle) wb.createCellStyle();

		// 设置边框
		headerStyle.setBorderTop(BorderStyle.THIN);
		headerStyle.setBorderBottom(BorderStyle.THIN);
		headerStyle.setBorderLeft(BorderStyle.THIN);
		headerStyle.setBorderRight(BorderStyle.THIN);
		// 字体加粗
		font.setBold(true);
		// 标题加粗
		headerStyle.setFont(font);

		cellStyle.setBorderTop(BorderStyle.THIN);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		//单元格垂直居中
		cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		//背景红色
		cancelStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
		cancelStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

		cancelStyle.setBorderTop(BorderStyle.THIN);
		cancelStyle.setBorderBottom(BorderStyle.THIN);
		cancelStyle.setBorderLeft(BorderStyle.THIN);
		cancelStyle.setBorderRight(BorderStyle.THIN);
		//单元格垂直居中
		cancelStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		//金色背景
		ignoreStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());
		ignoreStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);

		ignoreStyle.setBorderTop(BorderStyle.THIN);
		ignoreStyle.setBorderBottom(BorderStyle.THIN);
		ignoreStyle.setBorderLeft(BorderStyle.THIN);
		ignoreStyle.setBorderRight(BorderStyle.THIN);
		//单元格垂直居中
		ignoreStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

		//废弃的case
		int cancelNum = 0;
		//忽略的case
		int ignoreNum = 0;
		//多少列
		int columnNum = 0;
		//总共case 数
		int totalCase = list.size();

		for (int i = 0; i < totalCase; i++) {
			Class c = list.get(i).getClass();
			Field[] fs = c.getDeclaredFields();
			columnNum = fs.length;
			Row header = null;
			row = sheet.createRow(i + 1);
			//首先处理title
			if (i == 0) {
				header = sheet.createRow(0);
			}
			for (int j = 0; j < columnNum; j++) {
				Field f = fs[j];
				if (header != null) {
					//将bean里的字段名作为title 写在第一行
					Cell headerCell = header.createCell(j);
					headerCell.setCellValue(f.getName());
					headerCell.setCellStyle(headerStyle);
				}
				cell = row.createCell(j);
				try {
					//将bean 里字段的value 值作为内容写到excle里
					String value = f.get(list.get(i)).toString();
					cell.setCellValue(value);
					cell.setCellStyle(cellStyle);
					//根据comment 字段内容设置不同的背景色
					if (value.equals("cancel")) {
						cell.setCellStyle(cancelStyle);
						cancelNum++;
					}
					if (value.equals("ignore")) {
						cell.setCellStyle(ignoreStyle);
						ignoreNum++;
					}

				} catch (IllegalArgumentException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				} catch (IllegalAccessException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}

		//处理合并单元格
		int startRow = 1;
		int endRow = 0;
		for (int i = 1; i <= totalCase; i++) {
			row = sheet.getRow(i);
			cell = row.getCell(0);
			Row nextRow = sheet.getRow(i + 1);
			String nextValue = "";
			//处理最后一行的下一行
			if (nextRow != null) {
				Cell nextCell = nextRow.getCell(0);
				nextValue = nextCell.getStringCellValue();
			}
			String value = cell.getStringCellValue();
			if (!nextValue.equals(value)) {
				endRow = i;
				if (endRow > startRow) {
					/*					Logger.debug("startRow:" + startRow);
										Logger.debug("endRow:" + endRow);*/
					// 起始行号，终止行号， 起始列号，终止列号
					CellRangeAddress cellRangeAddress1 = new CellRangeAddress(
							startRow, endRow, 0, 0);
					CellRangeAddress cellRangeAddress2 = new CellRangeAddress(
							startRow, endRow, 1, 1);
					CellRangeAddress cellRangeAddress3 = new CellRangeAddress(
							startRow, endRow, 2, 2);
					CellRangeAddress cellRangeAddress4 = new CellRangeAddress(
							startRow, endRow, 3, 3);
					sheet.addMergedRegion(cellRangeAddress1);
					sheet.addMergedRegion(cellRangeAddress2);
					sheet.addMergedRegion(cellRangeAddress3);
					sheet.addMergedRegion(cellRangeAddress4);
				}
				startRow = i + 1;
			}
		}
		//添加title 过滤
		char[] endChar = Character.toChars('A' + (columnNum - 1));
		String rangeAddress = "A1:" + String.valueOf(endChar) + "1";
		sheet.setAutoFilter(CellRangeAddress.valueOf(rangeAddress));

		//添加结果分析
		row = sheet.createRow(totalCase + 1);
		cell = row.createCell(0);

		cell.setCellValue("Total");
		cell.setCellStyle(headerStyle);

		Cell cell1 = row.createCell(1);

		String result = "Test Suite:"
				+ (Integer) list.get(totalCase - 1).getCaseNum() + "||"
				+ "Total Case:" + totalCase + "||" + "Effect Case:"
				+ (totalCase - cancelNum - ignoreNum) + "||" + "Cancel Case:"
				+ cancelNum + "||" + "Ignore Case:" + ignoreNum;
		cell1.setCellValue(result);
		cell1.setCellStyle(headerStyle);

		// 设置为自动列宽
		for (int i = 0; i < columnNum; i++) {
			sheet.autoSizeColumn(i);
		}
		// 生成文件 路径+文件名
		File file = new File(strPath + fileName);
		FileOutputStream fos = null;
		try {
			fos = new FileOutputStream(file);
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		try {
			wb.write(fos);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (fos != null)
				try {
					fos.close();
					Logger.info("导出testCase到Desktop:" + fileName + "完成");
				} catch (IOException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
	}

	public static void main(String[] args) throws ClientProtocolException, IOException {

//		ExcleUtil excleUtil = new ExcleUtil();
//		excleUtil.create();
//		ArrayList<TestCaseBean> list=new ArrayList<>();
//		TestCaseBean testCaseBean = new TestCaseBean();
//		testCaseBean.setCaseDesc("1");
//		testCaseBean.setCaseName("2");
//		testCaseBean.setCaseNum(3);
//		testCaseBean.setComment("4");
//		testCaseBean.setSuiteName("5");
//		testCaseBean.setSutieDesc("6");
//		testCaseBean.setUrl("7");
//		list.add(testCaseBean);
//		
//		excleUtil.writeResult(list);
//		
		
		File file = new File(System.getProperty("user.home") + "\\Desktop\\" + "test.xlsx");
		String uploadFile = TestDataManager.UploadFile(file.getPath());
		System.out.println(uploadFile);
	
	}

}
